"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : 主备事务内，分别查询表后，主机进行表ddl修改操作后commit，验证备机表缓存是否一致
Create At   : 2023/5/19
Owner       : @peilinqian
Description :
    step1:打开gsc开关;
    step2:重启数据库生效;
    step3:创建数据库;
    step4:创建表，并插入数据;
    step5:以线程开启事务，主机对表进行alter操作后commit;
    step6:备机在主机事务执行ddl前后，分别进行表查询;
    step7:备机再次进行表查询;
Expect      :
    step1:打开gsc开关; expect:修改成功
    step2:重启数据库生效; expect:重启成功
    step3:创建数据库; expect:创建成功
    step4:创建表，并插入数据; expect:操作成功
    step5:以线程开启事务，主机对表进行alter操作后commit; expect: 操作成功
    step6:备机在主机事务执行ddl前后，分别进行表查询; expect:ddl前查询正确，ddl后查询报错
    step7:备机再次进行表查询; expect:查询结果正确
History     :
"""
import os
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.db_name = 'db_gsc0034'
        self.tb_name = "tb_gsc0034"
        self.index_name = "index_gsc0034"
        self.com = Common()
        self.constant = Constant()
        self.sta_sh = CommonSH('Standby1DbUser')

    def test_main(self):
        step_txt = '----step0:查看数据库状态是否正常;----'
        self.log.info(step_txt)
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)
        step_txt = '----step0:查看enable_global_syscache初始配置值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("enable_global_syscache")

        if self.init_para1 == 'off':
            step_txt = '----step1:打开gsc开关; expect:修改成功----'
            self.log.info(step_txt)
            result = pri_sh.execute_gsguc('set',
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_global_syscache= on")
            self.assertTrue(result, '执行失败:' + step_txt)

            step_txt = '----step2:重启数据库生效; expect:重启成功----'
            self.log.info(step_txt)
            restart = pri_sh.restart_db_cluster()
            self.assertTrue(restart, '执行失败:' + step_txt)
            self.is_reset_para = True
        else:
            step_txt = '----gsc开关设置正常，跳过step1、step2----'
            self.log.info(step_txt)
            self.is_reset_para = False

        step_txt = '----step3:创建数据库; expect:创建成功----'
        self.log.info(step_txt)
        create_db = f'drop database if exists {self.db_name};' \
            f'create database {self.db_name};'
        create_result = pri_sh.execut_db_sql(create_db)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, create_result,
                      '执行失败:' + step_txt)

        step_txt = '----step4:创建表，并插入数据; expect:操作成功----'
        self.log.info(step_txt)
        self.create_sql = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(jid int,jn int ,name int)" \
            f"partition by range (jid) subpartition by range(jn)(" \
            f"partition hr1 values less than(100)(" \
            f"subpartition hr11 values less than(100)," \
            f"subpartition hr12 values less than(300))," \
            f"partition hr2 values less than(300)(" \
            f"subpartition hr21 values less than(100)," \
            f"subpartition hr22 values less than(maxvalue)));" \
            f"select * from {self.tb_name};" \
            f"insert into {self.tb_name} select generate_series(1,200)," \
            f"generate_series(1,200), generate_series(1,200);"
        result = pri_sh.execut_db_sql(self.create_sql,
                                      dbname=f'{self.db_name}')
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败" + step_txt)
        self.assertIn('0 rows', result, "执行失败" + step_txt)
        self.log.info('----等待主备同步----')
        self.sta_sh.check_location_consistency('standby', node_num)

        step5_txt = '----step5:以线程开启事务，主机对表进行alter操作后commit，expect: 操作成功---'
        self.log.info(step5_txt)
        alter_sql = f'start transaction;' \
            f'select * from {self.tb_name} limit 1;' \
            f'select pg_sleep(10);' \
            f'alter table {self.tb_name} add (bak1 varchar(100),bak2 char);' \
            f'select * from {self.tb_name} limit 1;' \
            f'select pg_sleep(10);' \
            f'commit;'
        session1 = ComThread(pri_sh.execut_db_sql,
                             args=(alter_sql, '', self.db_name,))
        session1.setDaemon(True)
        session1.start()
        time.sleep(2)

        step_txt = '----step6:备机在主机事务执行ddl前后，分别进行表查询; ' \
                   'expect:ddl前查询正确，ddl后查询报错----'
        self.log.info(step_txt)
        alter_sql = f"start transaction;" \
            f"select * from {self.tb_name} limit 1;" \
            f"select pg_sleep(15);" \
            f"select * from {self.tb_name} limit 1;" \
            f"commit;"
        result = self.sta_sh.execut_db_sql(alter_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        err_flag = 'ERROR:  canceling statement due to conflict with recovery'
        suc_flag1 = 'jid | jn | name'
        self.assertIn(err_flag, result, "执行失败" + step_txt)
        self.assertEqual(suc_flag1, result.splitlines()[1].strip(),
                         "执行失败" + step_txt)

        step_txt = '----step7:备机再次进行表查询; expect:查询结果正确----'
        self.log.info(step_txt)
        select_sql = f"select * from {self.tb_name} limit 1;"
        result = self.sta_sh.execut_db_sql(select_sql,
                                           dbname=f'{self.db_name}')
        self.log.info(result)
        suc_flag2 = 'jid | jn | name | bak1 | bak2'
        self.assertEqual(suc_flag2, result.splitlines()[0].strip(),
                         "执行失败" + step_txt)

        session1.join()
        session1_result = session1.get_result()
        self.log.info(session1_result)
        self.assertEqual(suc_flag1, session1_result.splitlines()[1].strip(),
                         "执行失败" + step5_txt)
        self.assertEqual(suc_flag2, session1_result.splitlines()[12].strip(),
                         "执行失败" + step5_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step1_txt = '----清理数据库; expect:清理成功----'
        self.log.info(step1_txt)
        drop_cmd = f"drop database if exists {self.db_name};"
        drop_result = pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_result)

        if self.is_reset_para:
            self.step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
            self.log.info(self.step2_txt)
            self.msg1 = pri_sh.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG,
                f"enable_global_syscache ={self.init_para1}")
            self.step3_txt = '----重启数据库生效; expect:重启成功----'
            self.log.info(self.step3_txt)
            self.restart = pri_sh.restart_db_cluster()
            self.step4_txt = '----查询数据库状态; expect:状态正常----'
            self.log.info(self.step4_txt)
            self.status_result = pri_sh.get_db_cluster_status('status')

        step_txt = '----teardown断言----'
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_result,
                      "执行失败" + step_txt + step1_txt)
        if self.is_reset_para:
            self.assertTrue(self.msg1, '执行失败:' + self.step2_txt)
            self.assertTrue(self.restart, '执行失败:' + self.step3_txt)
            self.assertTrue(self.status_result, '执行失败:' + self.step4_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
